package com;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import javaBean.management_javaBean;

public class management_dao {

	public static Collection<management_javaBean> selectAll() {
		Collection<management_javaBean> mgVector = new ArrayList<management_javaBean>();
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = ConnectionPool.getConn();
		String sql = "select * from score";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				management_javaBean mg = new management_javaBean();
				mg.setId(rs.getString("id"));
				mg.setLanguage(rs.getInt("language"));
				mg.setMathematics(rs.getInt("mathematics"));
				mg.setEnglish(rs.getInt("english"));
				mgVector.add(mg);
			}
			System.out.println("查询所有学生成绩完毕！");
			ConnectionPool.close(rs, stmt, conn);
			return mgVector;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ConnectionPool.close(rs, stmt, conn);
		return mgVector;
	}

	public static management_javaBean selectBy(String id) {
		management_javaBean mg = null;
		Statement stmt = null;
		ResultSet rs = null;
		Connection conn = ConnectionPool.getConn();
		String sql = "select * from score where id='" + id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			mg = new management_javaBean();
			mg.setId(rs.getString("id"));
			mg.setLanguage(rs.getInt("language"));
			mg.setMathematics(rs.getInt("mathematics"));
			mg.setEnglish(rs.getInt("english"));
			System.out.println("查询学号为'" + mg.getId() + "'成绩完毕！");
			ConnectionPool.close(rs, stmt, conn);
			return mg;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ConnectionPool.close(rs, stmt, conn);
		return mg;
	}

	public static boolean delete(String id) {
		Statement stmt = null;
		int i = 0;
		Connection conn = ConnectionPool.getConn();
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			String sql = "delete from score where id='" + id + "'";
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ConnectionPool.close(stmt, conn);
		if (i > 0) {
			return true;
		} else {
			return false;
		}
	}

	public static boolean insert(management_javaBean mg) {
		Statement stmt = null;
		int i = 0;
		Connection conn = ConnectionPool.getConn();
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			String sql = "insert into score(id,language,mathematics,english) values('"
					+ mg.getId()
					+ "',"
					+ mg.getLanguage()
					+ ","
					+ mg.getMathematics() + "," + mg.getEnglish() + ")";
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		ConnectionPool.close(stmt, conn);
		if (i > 0) {
			return true;
		} else {
			return false;
		}
	}

	public static boolean update(management_javaBean mg, String oldid) {
		Statement stmt = null;
		int i = 0;
		Connection conn = ConnectionPool.getConn();
		try {
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_UPDATABLE);
			String sql = "update score set id='" + mg.getId() + "',language="
					+ mg.getLanguage() + ",mathematics=" + mg.getMathematics()
					+ ",english=" + mg.getEnglish() + " where id='" + oldid
					+ "' ";
			i = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		ConnectionPool.close(stmt, conn);
		if (i > 0) {
			return true;
		} else {
			return false;
		}
	}

}